package com.kitri.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.kitri.db.SetOfDataSource;
import com.kitri.domain.Subject;

public class SubjectDaoImpl implements SubjectDao {

	@Override
	public boolean insert(String subjectName) {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO SUBJECT (SUBJECT_ID, TITLE) ");
		sql.append("VALUES(concat(substr('J0000000', 1, 8-length(to_char(subject_seq.nextval))), ");
		sql.append("to_char(subject_seq.nextval)), ?)");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { subjectName });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean update(Subject subject) {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE SUBJECT SET TITLE = ? ");
		sql.append("WHERE SUBJECT_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { subject.getTitle(), subject.getSubjectId() });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean delete(String subjectId) {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM SUBJECT ");
		sql.append("WHERE SUBJECT_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { subjectId });
		if (result > 0)
			return true;
		return false;
	}

	@Override
	public List<Subject> selectAll() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM SUBJECT");
		sql.append(" ORDER BY title");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		List<Subject> list = new ArrayList<Subject>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString());

		for (Map<String, Object> row : rows) {
			Subject subject = new Subject();

			subject.setSubjectId((String) row.get("SUBJECT_ID"));
			subject.setTitle((String) row.get("TITLE"));

			list.add(subject);
		}

		return list;
	}

	@Override
	public List<Subject> selectOfCourse(String courseId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select subject.subject_id, title ");
		sql.append("from course, subject, juction_co_su ");
		sql.append("where course.course_id = juction_co_su.course_id ");
		sql.append("and subject.subject_id = juction_co_su.subject_id ");
		sql.append("and course.course_id = ? ");
		sql.append("order by title");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		List<Subject> list = new ArrayList<Subject>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql
				.toString(), new Object[] { courseId } );

		for (Map<String, Object> row : rows) {
			Subject subject = new Subject();

			subject.setSubjectId((String) row.get("SUBJECT_ID"));
			subject.setTitle((String) row.get("TITLE"));

			list.add(subject);
		}

		return list;
	}
}
